package sdfi.lee.Newpj.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;

import sdfi.lee.DAO.DAO;
import sdfi.lee.Newpj.Entity.NNotice;

public class NoticeDAO {
	public List<NNotice> getNotice(int pg,int size)
	{
		List<NNotice> list=new ArrayList<NNotice>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			
			con.setAutoCommit(false);
			String sql="select title,id,path,dt,type from (";
				sql+="select noticetitle as title,id as id,'path' as path,noticedate as dt,'0' as type from notice ";
				sql+=" union ";
				sql+="select rolltitle as title,id as id,rollpath as path,rolldate as dt,'1' as type from rollnotice where rolltype=1 ";
				sql+=") as nnews order by dt desc limit "+size*(pg-1)+","+size;
		
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				NNotice notice= new NNotice();
				notice.setDt(rs.getDate("dt"));
				notice.setId(rs.getInt("id"));
				notice.setPath(rs.getString("path"));
				notice.setTitle(rs.getString("title"));
				notice.setType(rs.getInt("type"));
				list.add(notice);
			}
			rs.close();
			con.close();
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

	public List<NNotice> getNotice(String id, int size,int type)
	{
		List<NNotice> list=new ArrayList<NNotice>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		try
		{
			
			con.setAutoCommit(false);
			String sql;
			if(type==0)
			{
			sql="select title,id,path,dt,type from (";
			sql+="select noticetitle as title,id as id,'path' as path,noticedate as dt,0 as type from notice where id>"+id+") as nt order by id asc ";
			}
			else 
			{
				sql="select title,id,path,dt,type from (";
				sql+="select rolltitle as title,id as id,rollpath as path,rolldate as dt,1 as type from rollnotice where rolltype=1 and id>"+id+") as nt order by id asc ";
			}
			sql+=" limit 0,5";
			ResultSet rs=con.createStatement().executeQuery(sql);
			con.commit();
			while(rs.next())
			{
				NNotice notice= new NNotice();
				notice.setDt(rs.getDate("dt"));
				notice.setId(rs.getInt("id"));
				notice.setPath(rs.getString("path"));
				notice.setTitle(rs.getString("title"));
				notice.setType(rs.getInt("type"));
				list.add(notice);
			}
			rs.close();
			con.close();
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
		}
		finally
		{
			DAO.closeSession();
		}
		return list;
	}

	public int getNoticeAccount()
	{
		String sql="select count(*) as c from (";
		sql+="select noticetitle as title,id as id,'path' as path,noticedate as dt,'0' as type from notice ";
		sql+=" union ";
		sql+="select rolltitle as title,id as id,rollpath as path,rolldate as dt,'1' as type from rollnotice where rolltype=1 ";
		sql+=") as nnews order by dt desc ";
		return DAO.getAccount(sql);
	}
	public static void main(String[] s)
	{
		System.out.println(new NoticeDAO().getNoticeAccount());
	}
}
